package dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class LocalidadeDAO {
	
	
	public static ResultSet getCidade(){
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet rsCidade = null;
		
	try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/profissionaisautonomos", "root", "mysql");
		
		String sql = "Select cd.Descricao, ed.Descricao from cidade cd, estado ed where cd.idEstado = ed.idEstado";
		//String sql = "Select Descricao, idEstado  from cidade";
		stm = conn.prepareStatement(sql);

		rsCidade = stm.executeQuery();
		
		/*stm.close();
		conn.close();*/
		
		return rsCidade;
		
	} catch (SQLException e) {
		System.out.println("sqlexception");
		e.printStackTrace();
	} catch (ClassNotFoundException e) {
		System.out.println("classnofoundexception");
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return null;
	
	}
	
	public static ResultSet getCidadeByDescricao(String descricao, int idEstado){
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet rsCidade = null;
		
	try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/profissionaisautonomos", "root", "mysql");
		
		String sql = "Select idCidade from cidade where descricao = '" + descricao + "' and idEstado = " + idEstado + ";";
		//String sql = "Select Descricao, idEstado  from cidade";
		stm = conn.prepareStatement(sql);

		rsCidade = stm.executeQuery();
		
		/*stm.close();
		conn.close();*/
		
		return rsCidade;
		
	} catch (SQLException e) {
		System.out.println("sqlexception");
		e.printStackTrace();
	} catch (ClassNotFoundException e) {
		System.out.println("classnofoundexception");
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return null;
	
	}
	
	public static ResultSet getEstado(){
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet rsEstado = null;
		
	try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/profissionaisautonomos", "root", "mysql");
		
		String sql = "Select Descricao from estado";
		stm = conn.prepareStatement(sql);

		rsEstado = stm.executeQuery();
		
		return rsEstado;
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return null;
	
	}
	
	public static ResultSet getEstadoByDescricao(String descricao){
		Connection conn = null;
		PreparedStatement stm = null;
		ResultSet rsEstado = null;
		
	try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/profissionaisautonomos", "root", "mysql");
		
		String sql = "Select idEstado from estado where descricao = '"+ descricao + "';";
		stm = conn.prepareStatement(sql);

		rsEstado = stm.executeQuery();
		
		return rsEstado;
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	return null;
	
	}
}
